package edu.uba.fcen.estimacion.database;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import edu.uba.fcen.estimacion.word.selection.constants.Constants;


public class DataBaseBuilder {

	private static final Logger logger = Logger.getLogger(DataBaseBuilder.class);
	private Connection connection;
	public static final String GEO_DB = "jdbc:hsqldb:file:src/main/resources/hsqldb/geo";
	public static final String WORDS_MEANS = "jdbc:hsqldb:mem:words";

	public DataBaseBuilder(String database) {
		try {
			Class.forName("org.hsqldb.jdbcDriver");
			this.connection = DriverManager.getConnection(database, "sa", "");
//			this.connection = DriverManager.getConnection("jdbc:hsqldb:mem:forbidenwords", "sa", "");
			logger.debug("The default schema was deleted");
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) throws Exception {
		new File("src/main/resources/hsdqldb/geo.data").delete();
		new File("src/main/resources/hsdqldb/geo.script").delete();
		DataBaseBuilder geolocation =new DataBaseBuilder(DataBaseBuilder.GEO_DB);
		geolocation.create();
		geolocation.count();
	}

	private void count() {
		try {
			Statement st = this.connection.createStatement();
			ResultSet rs = st.executeQuery("select * from Names");
			int count = 0;
			while(rs.next()) {
				count ++;
			}
			logger.debug("There are: " + count);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	private void addData() throws Exception {
		BufferedReader bufferReader;
		InputStreamReader [] files = {new InputStreamReader(this.getClass().getResourceAsStream("/hsqldb/cities1.sql"), Constants.ENCODING),
				new InputStreamReader(this.getClass().getResourceAsStream("/hsqldb/paises.sql"), Constants.ENCODING),
				new InputStreamReader(this.getClass().getResourceAsStream("/hsqldb/regions1.sql"), Constants.ENCODING),
				new InputStreamReader(this.getClass().getResourceAsStream("/hsqldb/nombres1.sql"), Constants.ENCODING),
				new InputStreamReader(this.getClass().getResourceAsStream("/hsqldb/forbidenWords.sql"), Constants.ENCODING)};
		Statement st = this.connection.createStatement();
		String line;
		//try {
			for (int i = 0; i < files.length; i++) {
				bufferReader = new BufferedReader(files[i]);
				boolean cut = false;
				while ((line = bufferReader.readLine()) != null && !cut) {
					try{
						st.executeQuery(line);
					} catch (SQLException e) {
						//cut = true;
						//logger.error("An exception occurred when try to insert: " + line);
						//logger.error(e.getMessage());
						
					}
				}
			}
			/*}finally {
			try {st.execute("SHUTDOWN;");} catch (Exception ex) {}
            try {connection.close();} catch (Exception ex) {} 
		}*/
	}

	private void createDB() {
		BufferedReader bufferReader;
		try {
			File db = new File("/hsqldb/geo.script");
			if (!db.exists()) {
				Reader in = new InputStreamReader(this.getClass().getResourceAsStream("/hsqldb/MySqlGeoNetMapImport.sql"), Constants.ENCODING);
				//FileReader fileReader = new FileReader("src/main/resources/hsqldb/MySqlGeoNetMapImport.sql");
				bufferReader = new BufferedReader(in);
				StringBuffer text = new StringBuffer();
				String line;
				while ((line = bufferReader.readLine()) != null) {
					text.append(line);
				}
				String[] commands = text.toString().split(";");
				for (String command : commands) {
					CallableStatement prepareCall;
					try {
						prepareCall = this.connection.prepareCall(command);
						prepareCall.execute();
						if (logger.isDebugEnabled()) {
							logger.debug("Executed: " + command);
						}
					} catch (SQLException e) {
						logger.error("Exception because table already exist");
					}
				}
			} else {
				logger.debug("The database already exist");
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}

	public Connection create() {
		this.createDB();
		try {
			this.addData();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return this.connection;
	}
}
